package com.spoto.oa.dao.impl;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.sql.Date;
import java.util.List;

import com.spoto.oa.dao.ProductDao;
import com.spoto.oa.dao.SalesDao;
import com.spoto.oa.model.Product;
import com.spoto.oa.model.Sales;
import com.spoto.oa.util.DB;

public class SalesDaoImpl implements SalesDao {

	private PreparedStatement pstm = null;
	private ResultSet rs = null;

	public void addSales(Sales sales) throws SQLException {
		Product product = sales.getProduct();
		pstm = DB
				.getConn()
				.prepareStatement(
						"insert into sales(statue,proCode,proName,date,creator,proType,proPrice,realPrice,numbers,remarks,month)"
								+ "values(?,?,?,?,?,?,?,?,?,?,?)");
		pstm.setInt(1, 1);
		pstm.setString(2, product.getCode());
		pstm.setString(3, product.getName());
		pstm.setDate(4, sales.getDate());
		pstm.setString(5, sales.getCreator());
		pstm.setString(6, product.getType());
		pstm.setFloat(7, product.getPrice());
		pstm.setFloat(8, sales.getRealPrice());
		pstm.setInt(9, sales.getNumbers());
		pstm.setString(10, sales.getRemarks());
		pstm.setInt(11, sales.getMonth());
		pstm.executeUpdate();

	}

	public List<Sales> getSalesByProType(String proType, String creator,
			boolean role) {
		try {
			pstm = DB.getConn().prepareStatement(
					"select * from sales  where proType=? and statue=1");
			if (!role) {
				pstm = DB
						.getConn()
						.prepareStatement(
								"select * from sales  where proType=? and creator=? and statue=1");
				pstm.setString(2, creator);
			}
			pstm.setString(1, proType);
			rs = pstm.executeQuery();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return resultSetToList(rs);
	}

	public void setInvalid(int id) {
		try {
			pstm = DB.getConn().prepareStatement(
					"update sales set statue=? where id=?");
			pstm.setInt(1, 0);
			pstm.setInt(2, id);
			pstm.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	public void updateSales(int id, Sales sales) {
		Product product = sales.getProduct();
		try {
			pstm = DB
					.getConn()
					.prepareStatement(
							"update sales set proCode=?,proName=?,date=?,creator=?,proType=?,"
									+ "proPrice=?,realPrice=?,numbers=?,remarks=?,month=? where id=?");
			pstm.setString(1, product.getCode());
			pstm.setString(2, product.getName());
			pstm.setDate(3, sales.getDate());
			pstm.setString(4, sales.getCreator());
			pstm.setString(5, product.getType());
			pstm.setFloat(6, product.getPrice());
			pstm.setFloat(7, sales.getRealPrice());
			pstm.setFloat(8, sales.getNumbers());
			pstm.setString(9, sales.getRemarks());
			pstm.setInt(10, sales.getMonth());
			pstm.setInt(11, id);
			pstm.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	public List<Sales> getSalesByProName(String proName, String creator,
			boolean role) {
		try {
			pstm = DB.getConn().prepareStatement(
					"select * from sales  where proName=? and statue=1");
			if (!role) {
				pstm = DB
						.getConn()
						.prepareStatement(
								"select * from sales  where proName=? and creator=? and statue=1");
				pstm.setString(2, creator);
			}
			pstm.setString(1, proName);
			rs = pstm.executeQuery();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return resultSetToList(rs);
	}

	public List<Sales> getSalesByProCode(String proCode, String creator,
			boolean role) {
		try {
			pstm = DB.getConn().prepareStatement(
					"select * from sales  where proCode=? and statue=1");
			if (!role) {
				pstm = DB
						.getConn()
						.prepareStatement(
								"select * from sales  where proCode=? and creator=? and statue=1");
				pstm.setString(2, creator);
			}
			pstm.setString(1, proCode);
			rs = pstm.executeQuery();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return resultSetToList(rs);
	}

	public List<Sales> getSalesByTime(Date dateBegin, Date dateEnd,
			String creator, boolean role) {
		try {
			pstm = DB.getConn().prepareStatement(
					"select * from sales  where date>? and date<?");
			if (!role) {
				pstm = DB
						.getConn()
						.prepareStatement(
								"select * from sales  where date>=? and date<=? and creator=? and statue=1");
				pstm.setString(3, creator);
			}
			pstm.setDate(1, dateBegin);
			pstm.setDate(2, dateEnd);
			rs = pstm.executeQuery();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return resultSetToList(rs);
	}

	private List<Sales> resultSetToList(ResultSet rs) {
		List<Sales> salesList = new ArrayList<Sales>();
		ProductDao proDao = new ProductDaoImpl();
		try {
			while (rs.next()) {
				Sales sales = new Sales();
				sales.setId(rs.getInt("id"));
				sales.setDate(rs.getDate("date"));
				sales.setCreator(rs.getString("creator"));
				sales.setMonth(rs.getInt("month"));
				sales.setNumbers(rs.getInt("numbers"));
				sales.setProduct(proDao.selectByCode(rs.getString("proCode")));
				sales.setRealPrice(rs.getFloat("realPrice"));
				sales.setRemarks(rs.getString("remarks"));
				sales.setStatue(rs.getInt("statue"));
				salesList.add(sales);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return salesList;
	}

	public List<Sales> getAllSales(String creator, boolean role) {
		try {
			pstm = DB.getConn().prepareStatement(
					"select * from sales where statue=1");
			if (!role) {
				pstm = DB.getConn().prepareStatement(
						"select * from sales  where statue=1 and creator=?");
				pstm.setString(1, creator);
			}
			rs = pstm.executeQuery();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return resultSetToList(rs);
	}

	public Sales getSalesById(int id) {
		try {
			pstm = DB.getConn().prepareStatement(
					"select * from sales where id=?");
			pstm.setInt(1, id);
			rs = pstm.executeQuery();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return resultSetToList(rs).get(0);
	}

	public List<Sales> getSalesByTime(Date dateBegin, Date dateEnd,
			String proType, String creator, boolean role) {
		try {
			pstm = DB
					.getConn()
					.prepareStatement(
							"select * from sales  where date>=? and date<=? and proType=?");
			if (!role) {
				pstm = DB
						.getConn()
						.prepareStatement(
								"select * from sales  where date>=? and date<=? and proType=? and creator=? and statue=1");
				pstm.setString(4, creator);
			}
			pstm.setDate(1, dateBegin);
			pstm.setDate(2, dateEnd);
			pstm.setString(3, proType);
			rs = pstm.executeQuery();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return resultSetToList(rs);
	}

	public List<Sales> getSalesByMonthAndType(int month, String proType,
			String creator, boolean role) {
		try {
			pstm = DB.getConn().prepareStatement(
					"select * from sales  where month=? and proType=?");
			if (!role) {
				pstm = DB
						.getConn()
						.prepareStatement(
								"select * from sales where month=? and proType=? and creator=? and statue=1");
				pstm.setString(3, creator);
			}
			pstm.setInt(1, month);
			pstm.setString(2, proType);
			rs = pstm.executeQuery();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return resultSetToList(rs);
	}

}
